package com.daxia.ogmall.dao;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.daxia.ogmall.common.ProductSellingStatus;
import com.daxia.ogmall.common.ProductSortType;
import com.daxia.ogmall.common.ProductStatus;
import com.daxia.ogmall.daobase.GenericDAOHibernate;
import com.daxia.ogmall.dto.BrowseRecordDTO;
import com.daxia.ogmall.dto.ProductDTO;
import com.daxia.ogmall.dto.ProductPropertiesDTO;
import com.daxia.ogmall.model.Product;
import com.daxia.ogmall.model.Specification;
import com.daxia.ogmall.support.Page;

@Repository
public class ProductDAO extends GenericDAOHibernate<Product> {

    public List<Product> find(ProductDTO dto, Page page) {
        StringBuilder hql = new StringBuilder();
        List<Object> paras = new ArrayList<Object>();
        hql.append("From Product n where 1 = 1 ");
        if (dto.getId() != null) {
            hql.append(" and n.id = ? ");
            paras.add(dto.getId());
        }
        if (dto.getStore() != null) {
        	if (dto.getStore().getId() != null) {
	            hql.append(" and n.store.id = ? ");
	            paras.add(dto.getStore().getId());
        	}
        }	
        if(dto.getFilterIds() != null && dto.getFilterIds().size() > 0) {
        	hql.append(" and n.id not in(");
        	int length = dto.getFilterIds().size();
        	for(int i = 0;i < length;i ++ ) {
        		hql.append("?");
        		if(i != length - 1) {
        			hql.append(",");
        		}
        		paras.add(dto.getFilterIds().get(i));
        	}
        	hql.append(")");
        }
        if (StringUtils.isNotBlank(dto.getName())) {
            hql.append(" and n.name like ? ");
            paras.add("%" + dto.getName() + "%");
        }					
        if (StringUtils.isNotBlank(dto.getExactName())) {
            hql.append(" and n.name = ? ");
            paras.add(dto.getExactName());
        }
        if (StringUtils.isNotBlank(dto.getSubTitle())) {
            hql.append(" and n.subTitle like ? ");
            paras.add("%" + dto.getSubTitle() + "%");
        }					
        if (dto.getProductCategory() != null) { 
        	if (dto.getProductCategory().getId() != null) {
	            hql.append(" and (n.productCategory.id = ? or n.productCategory.parentCategory.id = ? or n.productCategory.parentCategory.parentCategory.id = ? )");
	            paras.add(dto.getProductCategory().getId());
	            paras.add(dto.getProductCategory().getId());
	            paras.add(dto.getProductCategory().getId());
        	}
        }			
        if (dto.getSellingStatus() != null) {
        	if (dto.getSellingStatus() == ProductSellingStatus.OffSell.getValue()) {
        		hql.append(" and (n.sellingStatus = ? or n.sellingStatus is null)");
        		paras.add(dto.getSellingStatus());
        	} else {
        		hql.append(" and n.sellingStatus = ? ");
        		paras.add(dto.getSellingStatus());
        	}
        		
        }
        if (dto.getIsRecommend() != null) {
            hql.append(" and n.isRecommend = ? ");
            paras.add(dto.getIsRecommend());
        }
        if (dto.getIsNew() != null) {
        	hql.append(" and n.isNew = ? ");
        	paras.add(dto.getIsNew());
        }
        if (dto.getBrand() != null) {
        	if (dto.getBrand().getId() != null) {
	            hql.append(" and n.brand.id = ? ");
	            paras.add(dto.getBrand().getId());
        	}
        }			
        if (dto.getPinguanType() != null) {
        	hql.append(" and n.pinguanType = ? ");
        	paras.add(dto.getPinguanType());
        }
        if (dto.isFromAdmin()) {
        	if (dto.getStatus() != null) {
        		hql.append(" and n.status = ? ");
        		paras.add(dto.getStatus());
        	}
        } else {
        	if (dto.isAllStatus()) {
        	} else if (dto.getStatus() == null) {
        		hql.append(" and n.status = ? ");
        		paras.add(ProductStatus.Passed.getValue());
        	} else if (dto.getStatus() != null) {
        		hql.append(" and n.status = ? ");
        		paras.add(dto.getStatus());
        	}
        }
        if (dto.getUser() != null && dto.getUser().getId() != null) {
        	hql.append(" and  n.user.id = ? ");
        	paras.add(dto.getUser().getId());
        }
        if (!dto.isFromAdmin()) {
        	if (!dto.isAllStatus()) {
        		hql.append(" and n.sellingStatus = ? ");
        		paras.add(ProductSellingStatus.OnSell.getValue());
        	}
        }
        
        if (StringUtils.isNotBlank(dto.getSearchTerm())) {
        	hql.append(" and (n.name like ? "
        			+ " or n.subTitle like ? "
        			+ " or n.user.username like ? "
        			+ " or n.brand.name like ? "
        			+ " or n.productCategory.name like ? "
        			+ " or n.id in (select product.id from Specification where ogCode like ? ) )");
        	paras.add("%" + dto.getSearchTerm() + "%");
        	paras.add("%" + dto.getSearchTerm() + "%");
        	paras.add("%" + dto.getSearchTerm() + "%");
        	paras.add("%" + dto.getSearchTerm() + "%");
        	paras.add("%" + dto.getSearchTerm() + "%");
        	paras.add("%" + dto.getSearchTerm() + "%");
        }
        
        
        hql.append(" and n.deleted = 0 ");
        
        if (dto.getStartPrice() != null || dto.getEndPrice() != null) {
        	double factor = Specification.getFactor2();
        	if (dto.getStartPrice() != null) {
        		hql.append(" and n.id in (select s.product.id from Specification s where s.sellingPrice >= ?) ");
        		paras.add(dto.getStartPrice() / factor);
        	}
        	if (dto.getEndPrice() != null) {
        		hql.append(" and n.id in (select s.product.id from Specification s where s.sellingPrice <= ?) ");
        		paras.add(dto.getEndPrice() / factor);
        	}
        }
        
        if (dto.isFindByRandom()) {
        	hql.append(" order by rand() ");
        } else if (StringUtils.isNotBlank(dto.getOrderBy())) {
        	hql.append(" order by " + dto.getOrderBy() + " ");
        } else {
        	hql.append(" order by n.id desc ");
        }
        return super.find(hql.toString(), paras.toArray(), page);
    }
    
    /**
     * brandId,
     * startPrice,
     * endPrice,
     * searchTerm,
     * orderBy,
     * 
     * @param dto
     * @param page
     * @return
     */
    public List<Product> findForOnlineDesign(ProductDTO dto, Page page) {
    	StringBuilder hql = new StringBuilder("select distinct s.product from Specification s, Product p, ProductProperties pp where s.product.id = p.id and pp.specification.id = s.id ");
    	List<Object> paras = new ArrayList<Object>();
    	if (dto.getBrand() != null && dto.getBrand().getId() != null) {
    		hql.append(" and p.brand.id = ? ");
    		paras.add(dto.getBrand().getId());
    	}
    	if (dto.getStartPrice() != null || dto.getEndPrice() != null) {
        	double factor = Specification.getFactor2();
        	if (dto.getStartPrice() != null) {
        		hql.append(" and s.sellingPrice >= ? ");
        		paras.add(dto.getStartPrice() / factor);
        	}
        	if (dto.getEndPrice() != null) {
        		hql.append(" and s.sellingPrice <= ? ");
        		paras.add(dto.getEndPrice() / factor);
        	}
        }
    	if (dto.getProductCategory() != null) { 
         	if (dto.getProductCategory().getId() != null) {
         		hql.append(" and (p.productCategory.id = ? or p.productCategory.parentCategory.id = ? or p.productCategory.parentCategory.parentCategory.id = ? )");
 	            paras.add(dto.getProductCategory().getId());
 	            paras.add(dto.getProductCategory().getId());
 	            paras.add(dto.getProductCategory().getId());
         	}
         }
    	if (StringUtils.isNotBlank(dto.getSearchTerm())) {
    		hql.append(" and (p.name like ? "
    				+ " or p.subTitle like ? "
    				+ " or p.user.username like ? "
    				+ " or p.brand.name like ? "
    				+ " or p.productCategory.name like ? "
    				+ " or s.ogCode like ? )");
    		paras.add("%" + dto.getSearchTerm() + "%");
    		paras.add("%" + dto.getSearchTerm() + "%");
    		paras.add("%" + dto.getSearchTerm() + "%");
    		paras.add("%" + dto.getSearchTerm() + "%");
    		paras.add("%" + dto.getSearchTerm() + "%");
    		paras.add("%" + dto.getSearchTerm() + "%");
    	}
    	
    	if (StringUtils.isNotBlank(dto.getStyle())) {
    		hql.append(" and (pp.name = '风格' and pp.value like ?) ");
    		paras.add(dto.getStyle());
    	}
    	
    	hql.append(" and (p.deleted is null or p.deleted = 0) ");
    	
    	hql.append(" and p.sellingStatus = ? ");
    	paras.add(ProductSellingStatus.OnSell.getValue());
    	
    	hql.append(" and s.stock > 0 ");
    	hql.append(" and s.sellingPrice > 0 ");
    	
    	if (StringUtils.isNotBlank(dto.getOrderBy())) {
    		hql.append(" order by " + dto.getOrderBy() + " ");
    	}
    	return super.find2(hql.toString(), paras.toArray(), page);
    }
    
    /**
     * 生成from之后的sql，用来提供查询，因为有两类数据需要提供查询，分别是产品查询和品牌查询
     * @param properties
     * @param dto
     * @param paras
     * @return
     */
    private String generateSql(List<ProductPropertiesDTO> properties,ProductDTO dto,List<Object> paras) {  //生成from之后的东西，因为有可能要查到
    	StringBuilder sql = new StringBuilder();  
        sql.append(" select n.id,s.sellingprice,n.brand_id,n.totalSold,s.id as specificationId  From product n join productcategory g join specification s join brand b join user u ");
        List<String> tablePrex = new Vector<String>();
        int index = 0;
        if(properties != null && properties.size() > 0) {    //组装properties的子查询代码
        	int size = properties.size();
        	for(int i = 0;i < size;i ++ ) {
        		index ++;
        		ProductPropertiesDTO aProp = properties.get(i);
        		String prefix = "p"+index;
        		sql.append(" join (SELECT * FROM productproperties WHERE 1=1 ");
        		if(aProp.getName() != null) {
        			sql.append(" and name = ? ");
        			paras.add(aProp.getName());
        		}
        		if(aProp.getValue() != null) {
        			sql.append(" and value like ? ");
        			paras.add("%"+aProp.getValue()+"%");
        		}
        		sql.append(")").append(prefix); 
        		tablePrex.add(prefix);
        	}
        }
        sql.append(" where n.ProductCategory_id = g.id and s.product_id = n.id and n.id=s.product_id and n.brand_id = b.id and n.user_id = u.id ");
        sql.append(" and n.sellingStatus =  " + ProductSellingStatus.OnSell.getValue() + " ");
        for(String thePrefix:tablePrex) {   //组装结合条件
        	sql.append(" and s.id = ").append(thePrefix).append(".specification_id ");
        }
        if(dto != null) {
        	 if(StringUtils.isNotBlank(dto.getCategoryName())) {  //分类名称,要支持两种查询，一种是标题，一种是分类+属性信息，还有一种是标题查询
        		 sql.append("and (");
        		 List<String> propNames = dto.getPropertyNames();
        		 //先是标题查询,如：床 白色 就是 标题必须得同时含有床+白色的商品 ,然后是类别查询,是床类别的白色属性的
        		 /*sql.append("( n.name like ? "); 
        		 paras.add("%"+dto.getCategoryName()+"%");
        		 if(propNames != null && propNames.size() > 0) {
        			 for(int j = 0;j < propNames.size();j ++ ) {
        				 sql.append(" and n.name like ? ");
        				 paras.add("%"+propNames.get(j)+"%");
        			 }
        		 }
        		 sql.append(") or (");*/
        		 sql.append(" g.name like ? or g.parent_category_id in(select g1.id from productcategory g1 where g1.name like ?) ");
        		 sql.append(" or g.parent_category_id in(select g2.id from productcategory g2,productcategory g3 where g2.parent_category_id = g3.id and g3.name like ? ) ");
        		 paras.add("%"+dto.getCategoryName()+"%");
        		 paras.add("%"+dto.getCategoryName()+"%");
        		 paras.add("%"+dto.getCategoryName()+"%");
        		/* if(propNames != null && propNames.size() > 0) {
        			 sql.append(" and (");
	        			 for(int j = 0;j < propNames.size();j ++ ) {
	        				 if(j > 0) {
	        					 sql.append(" or ");
	        				 }
	        				 sql.append(" pp.propertyValue like ? ");
	        				 paras.add("%"+propNames.get(j)+"%");
	        			 }
        			 sql.append(") ");
        		 }*/
        		 sql.append(") ");
        		 //再是规格查询，如：床 白色 就是床分类下面的白色属性商品
        	 }
        	 if (dto.getBrand() != null) { 
             	if (dto.getBrand().getId() != null) { 
     	            sql.append(" and n.brand_id = ? ");
     	            paras.add(dto.getBrand().getId());
             	}
             }
        	 if (dto.getId() != null) {
        		 sql.append(" and n.id = ? ");
                 paras.add(dto.getId());
             }
             if (dto.getStore() != null) {
             	if (dto.getStore().getId() != null) {
             		sql.append(" and n.store_id = ? ");
     	            paras.add(dto.getStore().getId());
             	}
             }			
             if ((!StringUtils.isNotBlank(dto.getCategoryName())) && StringUtils.isNotBlank(dto.getName())) {  //传入的名称查询，产品名称，产品副标题，属性，工厂编码，规格编码，规格1，规格2
            	 sql.append(" and (n.name like ? or n.subTitle like ? or s.ogcode like ? or s.spec1 like ? or s.spec2 like ? or s.factoryCode like ? or b.name like ? or s.id in (select  p1.specification_id from productproperties p1 where p1.value like ?) )");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
                 paras.add("%" + dto.getName() + "%");
             }					
             if (StringUtils.isNotBlank(dto.getExactName())) {
            	 sql.append(" and n.name = ? ");
                 paras.add(dto.getExactName());
             }
             if (StringUtils.isNotBlank(dto.getSubTitle())) {
            	 sql.append(" and n.subTitle like ? ");
                 paras.add("%" + dto.getSubTitle() + "%");
             }					
             if (dto.getProductCategory() != null) { 
             	if (dto.getProductCategory().getId() != null) {
             		sql.append(" and (n.ProductCategory_id = ? or g.parent_category_id = ? or g.parent_category_id in(select id from productcategory c where c.parent_category_id = ? ) )");
     	            paras.add(dto.getProductCategory().getId());
     	            paras.add(dto.getProductCategory().getId());
     	            paras.add(dto.getProductCategory().getId());
             	}
             }			
             if (dto.getSellingStatus() != null) {
            	 sql.append(" and n.sellingStatus = ? ");
                 paras.add(dto.getSellingStatus());
             }
             if (dto.getIsRecommend() != null) {
            	 sql.append(" and n.isRecommend = ? ");
                 paras.add(dto.getIsRecommend());
             }
             if (dto.getIsNew() != null) {
            	 sql.append(" and n.isNew = ? ");
             	paras.add(dto.getIsNew());
             }
             if (dto.getBrand() != null) {
             	if (dto.getBrand().getId() != null) {
             		sql.append(" and n.brand_id = ? ");
     	            paras.add(dto.getBrand().getId());
             	}
             }			
             if (dto.getPinguanType() != null) {
            	 sql.append(" and n.pinguanType = ? ");
             	paras.add(dto.getPinguanType());
             }
             if(dto.getStartPrice() != null) {
            	 Double priceFactor = dto.getPriceFactor();   //乘以当前用户的系数，并且在sql中还得取出对应的产品的用户的价格系数，生成的sql如：and s.sellingPrice*IFNULL(u.pricefactor,1)*2 >= ?
            	 sql.append(" and s.sellingPrice*IFNULL(u.pricefactor,1)*"+priceFactor+" >= ? ");
            	 paras.add(dto.getStartPrice());
             }
             if(dto.getEndPrice() != null) {
            	 Double priceFactor = dto.getPriceFactor();   //乘以当前用户的系数，并且在sql中还得取出对应的产品的用户的价格系数
            	 sql.append(" and s.sellingPrice*IFNULL(u.pricefactor,1)*"+priceFactor+" <= ? ");
            	 paras.add(dto.getEndPrice());
             }
             List<Long> brandIds = dto.getBrandIds();
             if(brandIds != null && brandIds.size() > 0) {   //多个品牌选择
            	 sql.append(" and n.brand_id in(");
            	 int size = brandIds.size();
            	 for(int i = 0;i < size;i ++ ) {
            		 if(i < size-1) {
            			 sql.append("?,");
            		 } else {
            			 sql.append("?");
            		 }
            		 paras.add(brandIds.get(i));
            	 }
            	 sql.append(")");
             }
             
             if (dto.getLiveDirectBrandIds() != null && dto.getLiveDirectBrandIds().size() > 0) {
            	 sql.append(" and n.brand_id in (? ").append(StringUtils.repeat(", ?", dto.getLiveDirectBrandIds().size() - 1)).append(") ");
            	 paras.addAll(dto.getLiveDirectBrandIds());
             }
             
             if (dto.isAllStatus()) {
            	 
             } else if (dto.getStatus() == null) {
            	 sql.append(" and n.status = ? ");
             	paras.add(ProductStatus.Passed.getValue());
             } else if (dto.getStatus() != null) {
            	 sql.append(" and n.status = ? "); 
             	paras.add(dto.getStatus());
             }
        } 
        
        
       sql.append(" and n.deleted = 0 ");
       
        
        if (dto.getSortType() != null && dto.getSortType().intValue() == ProductSortType.New.getValue()) {
        	sql.append(" order by n.id desc ");
        } else if(dto.getSortType() != null && dto.getSortType().intValue() == ProductSortType.Price.getValue()) {  
        	sql.append(" order by s.sellingPrice asc ");
        } else {
        	sql.append(" order by n.totalSold desc ");
        } 
        return sql.toString();          
    }
    
    /**
     * 最终组装的sql如；
     * SELECT DISTINCT t.id FROM product t,productcategory g,(SELECT * FROM productproperties WHERE NAME="灯头个数" AND VALUE="1个") p1,
		(SELECT * FROM productproperties WHERE NAME="风格" AND VALUE="中式") p2
		WHERE t.ProductCategory_id = g.id and t.t.id=p1.product_id
		AND t.id= p2.product_id
		+其他条件
     * @param properties
     * @param product
     * @return
     */
    public List getProductByProperties(List<ProductPropertiesDTO> properties,ProductDTO dto,Page page) {  //根据传入的参数和内容来进行查询
    	List<Object> paras = new Vector<Object>(); 
    	String sql = "select distinct f.id from ("+ generateSql(properties, dto,paras)+") f";
    	Object[] paramArr = new Object[paras.size()];
        for(int i = 0;i < paras.size();i ++ ) {
        	paramArr[i] = paras.get(i); 
        } 
        List<Long> ids = new Vector<Long>();
        List<Object[]> idArrs = super.executeSQLQuery(sql.toString(), paramArr,page); 
        if(idArrs != null && idArrs.size() > 0) {
        	for(Object thePro:idArrs) {
        		if(thePro instanceof Long) {
        			ids.add((Long) thePro);
        		} else if(thePro instanceof BigInteger) {
        			ids.add(((BigInteger)thePro).longValue());
        		} else if(thePro instanceof BigDecimal) {
        			ids.add(((BigDecimal)thePro).longValue());
        		}
        	}
        }
        return ids;
    }
    
    public List getDistictSpecifation(List<ProductPropertiesDTO> properties,ProductDTO dto,Page page) {  //根据参数和数据内容来查询类别（由于涉及到价格参数等，还是需要将规格查询出来）
    	List<Object> paras = new Vector<Object>();
    	String sql = "select distinct f.specificationId from ("+ generateSql(properties, dto,paras)+") f ";
    	Object[] paramArr = new Object[paras.size()];
        for(int i = 0;i < paras.size();i ++ ) {
        	paramArr[i] = paras.get(i);  
        } 
        List<Long> ids = new Vector<Long>();
        List<Object[]> idArrs = super.executeSQLQuery(sql.toString(), paramArr,page); 
        if(idArrs != null && idArrs.size() > 0) {
        	for(Object thePro:idArrs) {
        		if(thePro instanceof Long) {
        			ids.add((Long) thePro);
        		} else if(thePro instanceof BigInteger) {
        			ids.add(((BigInteger)thePro).longValue());
        		} else if(thePro instanceof BigDecimal) {
        			ids.add(((BigDecimal)thePro).longValue());
        		}
        	}
        }
        return ids;
    }
    
    public List getDistinctBrand(List<ProductPropertiesDTO> properties,ProductDTO dto) {  //根据参数和数据内容来查询产品涉及到的品牌数据
    	List<Object> paras = new Vector<Object>();
    	String sql = "select distinct f.brand_id from ("+ generateSql(properties, dto,paras)+") f ";
    	Object[] paramArr = new Object[paras.size()];
        for(int i = 0;i < paras.size();i ++ ) {
        	paramArr[i] = paras.get(i);  
        } 
        List<Long> ids = new Vector<Long>();
        List<Object[]> idArrs = super.executeSQLQuery(sql.toString(), paramArr,null); 
        if(idArrs != null && idArrs.size() > 0) {
        	for(Object thePro:idArrs) {
        		if(thePro instanceof Long) {
        			ids.add((Long) thePro);
        		} else if(thePro instanceof BigInteger) {
        			ids.add(((BigInteger)thePro).longValue());
        		} else if(thePro instanceof BigDecimal) {
        			ids.add(((BigDecimal)thePro).longValue());
        		}
        	}
        }
        return ids;
    }
    
    public List<Product> getProductByIds(List<Long> ids) {
    	StringBuilder hql = new StringBuilder();
    	hql.append(" from Product where id in(:ids) ");
    	Query query = getSession().createQuery(hql.toString());
    	query.setParameterList("ids", ids);
    	return query.list();
    }
    
    public List<Product> search(ProductDTO dto, Page page) {
    	StringBuilder hql = new StringBuilder();
    	List<Object> paras = new ArrayList<Object>();
    	
    	hql.append("select n.product From Specification n where 1 = 1 ");
    	
    	if (dto.getProductCategory() != null) {
        	if (dto.getProductCategory().getId() != null) {
	            hql.append(" and n.product.productCategory.id = ? ");
	            paras.add(dto.getProductCategory().getId());
        	}
        }			
    	
    	hql.append(" and n.product.deleted = 0 ");
    	
    	return super.find(hql.toString(), paras.toArray(), page);
    }
    
    
    public Product findOne(ProductDTO dto) {
		Page page = new Page();
		page.setPageSize(1);
		page.setPage(1);
		
		List<Product> list = this.find(dto, page);
	    return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }
    
    @Override
    public Long create(Product t) {
    	if (t.getStatus() == null) {
    		t.setStatus(ProductStatus.NotReview.getValue());
    	}
    	if (t.getCreateTime() == null) {
    		t.setCreateTime(new Date());
    	}
    	return super.create(t);
    }
    
    @Override
    public void update(Product t) {
    	t.setLastUpdateTime(new Date());
    	super.update(t);
    }
    
    public int addTotalSold(Long id) {
    	StringBuffer hqlBuffer = new StringBuffer();
    	hqlBuffer.append("update Product set totalSold = totalSold + 1 where id = ? ");
    	Object[] paras = new Object[]{id};
    	return super.executeUpdate(hqlBuffer.toString(), paras);
    }
    
    public List<Product> find2(ProductDTO dto, Page page) {
        StringBuilder hql = new StringBuilder();
        List<Object> paras = new ArrayList<Object>();
        hql.append("From Product n  where 1 = 1 ");
        if (dto.getId() != null) {
            hql.append(" and n.id = ? ");
            paras.add(dto.getId());
        }
        if (dto.getStore() != null) {
        	if (dto.getStore().getId() != null) {
	            hql.append(" and n.store.id = ? ");
	            paras.add(dto.getStore().getId());
        	}
        }			
        if (StringUtils.isNotBlank(dto.getName())) {
            hql.append(" and n.name like ? ");
            paras.add("%" + dto.getName() + "%");
        }					
        if (StringUtils.isNotBlank(dto.getExactName())) {
            hql.append(" and n.name = ? ");
            paras.add(dto.getExactName());
        }
        if (StringUtils.isNotBlank(dto.getSubTitle())) {
            hql.append(" and n.subTitle like ? ");
            paras.add("%" + dto.getSubTitle() + "%");
        }					
        if (dto.getProductCategory() != null) {
        	if (dto.getProductCategory().getId() != null) {
	            hql.append(" and n.productCategory.id = ? ");
	            paras.add(dto.getProductCategory().getId());
        	}
        }			
        if (dto.getSellingStatus() != null) {
            hql.append(" and n.sellingStatus = ? ");
            paras.add(dto.getSellingStatus());
        }
        if (dto.getIsRecommend() != null) {
            hql.append(" and n.isRecommend = ? ");
            paras.add(dto.getIsRecommend());
        }
        if (dto.getIsNew() != null) {
        	hql.append(" and n.isNew = ? ");
        	paras.add(dto.getIsNew());
        }
        if (dto.getBrand() != null) {
        	if (dto.getBrand().getId() != null) {
	            hql.append(" and n.brand.id = ? ");
	            paras.add(dto.getBrand().getId());
        	}
        }			
        if (dto.getPinguanType() != null) {
        	hql.append(" and n.pinguanType = ? ");
        	paras.add(dto.getPinguanType());
        }
        if (dto.isAllStatus()) {
        } else {
        	hql.append(" and n.status = ? ");
        	paras.add(ProductStatus.Passed.getValue());
        }
        
        hql.append(" and n.deleted = 0 ");
        
        if (dto.isFindByRandom()) {
        	hql.append(" order by rand() ");
        }
        return super.find(hql.toString(), paras.toArray(), page);
    }

	public void deleteByUserId(Long id) {
		super.executeUpdate("delete from Product where user.id = ?", new Object[] {id});
	}
	
	public List<Product> findByBrowsedProduct(BrowseRecordDTO dto,Page page) {
    	StringBuilder hql = new StringBuilder();
        List<Object> paras = new ArrayList<Object>();
        hql.append("select distinct n.specification.product from BrowseRecord n where 1=1 ");
        if (dto.getUser() != null) {
        	if (dto.getUser().getId() != null) {
	            hql.append(" and n.user.id = ? ");
	            paras.add(dto.getUser().getId());
        	}
        }			
        if (dto.getSpecification() != null) {
        	if (dto.getSpecification().getId() != null) {
	            hql.append(" and n.specification.id = ? ");
	            paras.add(dto.getSpecification().getId());
        	}
        }
        hql.append(" and n.specification.product.deleted = 0 ");
        
        hql.append(" order by n.id desc ");
        return super.find(hql.toString(), paras.toArray(), page);
    }

	public void cleanup() {
		super.executeSQLUpdate("delete from specification where product_id not in (select id from product)", null);
		super.executeSQLUpdate("delete from productproperties where specification_id not in (select id from specification)", null);
	}
    
}
